﻿using HCQ2_IBLL;
using HCQ2_Model;
using HCQ2_Model.SchoolClass;
using HCQ2_Model.Student;
using HCQ2_Model.ViewModel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using HCQ2_Common;
using HCQ2_Model.SelectModel;
using System.Data;
using HCQ2_Model.ViewModel.SysAdmin;
using System.Diagnostics;
using HCQ2_Common.Log;
using HCQ2_Common.SQL;
using HCQ2_Common.Encrypt;

namespace HCQ2_BLL
{
    /// <summary>
    /// 学生相关
    /// </summary>
    public partial class T_StudentBLL:IT_StudentBLL
    {
        /// <summary>
        /// 学号集合，用于导入时读取所有学号避免导入验证重复查询数据库次数过多
        /// </summary>
        private List<StudentId> studentIdList;

        /// <summary>
        /// 获取学生列表
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public TableModel GetStudentList(StudentListQuery query)
        {
            return DBSession.IT_StudentDAL.GetStudentList(query);
        }

        /// <summary>
        /// 新增学生信息
        /// </summary>
        /// <param name="edit"></param>
        /// <returns></returns>
        public int addStudent(StudentEdit edit)
        {
            T_Student student = edit.MapTo<T_Student>();
            student.updateTime = DateTime.Now;
            student.createTime = DateTime.Now;
            student.createUser = edit.updateUser;

            student = setTeacherName(student);
            DBSession.IT_StudentDAL.Add(student);

            

            return student.id;
        }

        /// <summary>
        /// 编辑学生信息
        /// </summary>
        /// <param name="edit"></param>
        /// <returns></returns>
        public int updateStudent(StudentEdit edit)
        {
            T_Student student = edit.MapTo<T_Student>();
            student.updateTime = DateTime.Now;

            student = setTeacherName(student);

            DBSession.IT_StudentDAL.Modify(student,w=>w.id==edit.id,
                "schoolName", "classId", "studentName", "studentId", "teacherName", "major","sex","tel","email","idCode","state", "updateUser", "updateTime");

            //修改账号状态
            T_User user = DBSession.IT_UserDAL.FindSingle(w=>w.login_name.Equals(edit.studentId));
            user.status = edit.state.Value;
            DBSession.IT_UserDAL.Modify(user,w=>w.user_id==user.user_id,"status");
            return edit.id.Value;
        }

        /// <summary>
        /// 删除学生
        /// </summary>
        /// <param name="id"></param>
        public void delStudent(int id)
        {
            //学生信息
            T_Student student = DBSession.IT_StudentDAL.FindSingle(w => w.id == id);
            //学生账号信息
            T_User studentUser = DBSession.IT_UserDAL.FindSingle(w=>w.studentId.Equals(student.studentId));

            //删除账号对应角色关联
            DBSession.IT_UserRoleRelationDAL.Delete(w=>w.user_id==studentUser.user_id);
            //删除对应的账号
            DBSession.IT_UserDAL.Delete(w => w.login_name.Equals(student.studentId));
            //删除学生
            DBSession.IT_StudentDAL.Delete(w => w.id == id);
        }
        private void checkStudentId(Nullable<int> id,string studentId) {
            T_Student student = DBSession.IT_StudentDAL.FindSingle(w=>w.studentId ==studentId);
            string mess = string.Format("学号：{0} 已经存在，由于需要通过学号生成账号，所以学号不能重复", studentId);
            if (id == null)
            {
                if (student != null)
                {
                    throw new BllMessageException(mess);
                }
            }
            if (student != null && student.id != id) {
                throw new BllMessageException(mess);
            }
        }

        private T_Student setTeacherName(T_Student student) {
            T_Class classData = DBSession.IT_ClassDAL.FindSingle(w => w.id == student.classId);
            student.teacherName = classData.teacherName;
            return student;
        }

        /// <summary>
        /// 加载所有学号，用于导入，学生数据少时使用
        /// </summary>
        private void loadStudentIdList()
        {
            studentIdList = SqlHelper.ExecuteList<StudentId>("select studentId from T_Student");
        }
        /// <summary>
        /// 获取学校下面的班级
        /// </summary>
        /// <param name="schoolName"></param>
        /// <returns></returns>
        public List<ListBoxModel> ListBySchoolClass(string schoolName)
        {
            return DBSession.IT_StudentDAL.ListBySchoolClass(schoolName);
        }

        /// <summary>
        /// 导入学生
        /// </summary>
        /// <param name="tempPath">导入文件</param>
        /// <param name="schoolName">学校名称</param>
        /// <param name="classId">班级名称</param>
        /// <param name="currentUserId">操作用户</param>
        public List<String> importStudent(string tempPath, string schoolName, int classId, string currentUser,ref List<UserModel> userModel)
        {
            //获取导入文件里面的数据
            Stopwatch sw = new Stopwatch();
            sw.Start();

            List<string> valid = new List<string>();
            DataTable excelData = NpoiHelper.ExcelToDataTable(tempPath, true, 0);
            if (excelData == null) {
                valid.Add("无法获取到excel文件中数据");
            }
            sw.Stop();
            LogHelper.InfoLog(string.Format("导入学生数据，读取Excel数据耗时：{0} 毫秒。",sw.ElapsedMilliseconds));
            sw.Restart();

            loadStudentIdList();

            valid = checkRequired(excelData);
            sw.Stop();
            LogHelper.InfoLog(string.Format("导入学生数据，校验Excel数据耗时：{0} 毫秒。", sw.ElapsedMilliseconds));
            if (valid.Count > 0) {
                return valid;
            }

            sw.Restart();
            DataTable importData = toImportDataTable(excelData,schoolName,classId,currentUser);
            sw.Stop();
            LogHelper.InfoLog(string.Format("导入学生数据，转换为导入模型数据耗时：{0} 毫秒。", sw.ElapsedMilliseconds));
            sw.Restart();
            HCQ2_Common.SQL.SqlHelper.SqlBulkCopy(importData, "T_Student");
            sw.Stop();
            LogHelper.InfoLog(string.Format("导入学生数据，批量插入数据库：{0}条，耗时：{1} 毫秒。",importData.Rows.Count, sw.ElapsedMilliseconds));

            sw.Restart();
            addStudentUserList(excelData,schoolName,ref userModel);
            sw.Stop();
            LogHelper.InfoLog(string.Format("导入学生数据，生成创建账号名单：{0}条，耗时：{1} 毫秒。", importData.Rows.Count, sw.ElapsedMilliseconds));
            return valid;
        }

        /// <summary>
        /// 数据转换为导入模型的数据
        /// </summary>
        /// <param name="excelData">excel文件数据</param>
        /// <param name="schoolName">所属学校</param>
        /// <param name="classId">所属班级</param>
        /// <param name="currentUser">当前用户</param>
        /// <returns></returns>
        private DataTable toImportDataTable(DataTable excelData, string schoolName, int classId, string currentUser)
        {
            DataTable importModel = importStudentModel();

            T_Class classData = DBSession.IT_ClassDAL.FindSingle(w=>w.id==classId);

            DateTime date = DateTime.Now;

            foreach (DataRow row in excelData.Rows) {
                DataRow newRow = importModel.NewRow();

                newRow["schoolName"] = schoolName;
                newRow["classId"] = classId;
                newRow["teacherName"] = classData.teacherName;
                newRow["studentName"] =Convert.ToString(row["F1"]);
                newRow["studentId"] = Convert.ToString(row["F2"]);
                newRow["major"] = Convert.ToString(row["F4"]);
                if (row["F5"] != null && !row["F5"].ToString().Equals(""))
                {
                    newRow["sex"] = Convert.ToBoolean(row["F5"]);
                }
                newRow["tel"] = Convert.ToString(row["F6"]);
                newRow["email"] = Convert.ToString(row["F7"]);
                newRow["idCode"] = Convert.ToString(row["F3"]);
                newRow["photo"] = null;
                newRow["state"] = 1;
                newRow["updateTime"] = date;
                newRow["updateUser"] = currentUser;
                newRow["createTime"] = date;
                newRow["createUser"] = currentUser;

                importModel.Rows.Add(newRow);
            }

            return importModel;
        }

        /// <summary>
        /// 导入模型，位置顺序需要跟数据库表字段顺序一样
        /// </summary>
        /// <returns></returns>
        private DataTable importStudentModel()
        {
            DataTable dt = new DataTable();
            dt.Columns.AddRange(new DataColumn[] {
            new DataColumn("id",typeof(int)),
            new DataColumn("schoolName",typeof(string)),//学校
            new DataColumn("classId",typeof(int)),//班级ID
            new DataColumn("teacherName",typeof(string)),//老师名称
            new DataColumn("studentName",typeof(string)),//学生姓名
            new DataColumn("studentId",typeof(string)),//学号
            new DataColumn("major",typeof(string)),//专业
            new DataColumn("sex", typeof(bool)),//性别
            new DataColumn("tel",typeof(string)),//电话
            new DataColumn("email",typeof(string)),//邮箱
            new DataColumn("idCode",typeof(string)),//身份证

            new DataColumn("photo",typeof(string)),
            new DataColumn("state",typeof(int)),
            new DataColumn("updateTime",typeof(DateTime)),
            new DataColumn("updateUser",typeof(string)),
            new DataColumn("createTime",typeof(DateTime)),
            new DataColumn("createUser",typeof(string))
            });

            return dt;
        }
        /// <summary>
        /// 验证必填信息是否为空
        /// </summary>
        /// <param name="excelData"></param>
        /// <returns></returns>
        private List<string> checkRequired(DataTable excelData)
        {
            List<string> valid = new List<string>();
            for (int i = 0; i < excelData.Rows.Count; i++) {
                int rowNum = (i+1+1);
                if (excelData.Rows[i]["F1"] == null) {
                    valid.Add(string.Format("第{0}行，姓名不能为空",rowNum));
                }
                if (excelData.Rows[i]["F2"] == null)
                {
                    valid.Add(string.Format("第{0}行，学号不能为空", rowNum));
                }
                else if (checkStudentId(Convert.ToString(excelData.Rows[i]["F2"]))) {
                    valid.Add(string.Format("第{0}行，学号已经存在系统中", rowNum));
                }
            }

            return valid;
        }

        /// <summary>
        /// 创建学生对应的账号
        /// </summary>
        /// <param name="userList"></param>
        public void createStudentUser(List<UserModel> userList)
        {
            string code = Guid.NewGuid().ToString();
            DataTable userImportData = importUserModel(userList,code);

            SqlHelper.SqlBulkCopy(userImportData,"T_User");

            setStudentUserRole(code);
        }

        /// <summary>
        /// 为生成的学生账号创建角色关联
        /// </summary>
        /// <param name="code">批量导入代码</param>
        private void setStudentUserRole(string code)
        {
            string sql = string.Format(@"insert into T_UserRoleRelation (user_id,role_id)
            select user_id,9 role_id  from T_User where user_note='{0}'
            ",code);

            SqlHelper.ExecuteScalar(sql);
        }


        /// <summary>
        /// 用户表对应模型
        /// </summary>
        /// <returns></returns>
        private DataTable importUserModel(List<UserModel> userList,string importCode)
        {
            DataTable dt = new DataTable();
            dt.Columns.AddRange(new DataColumn[] {
            new DataColumn("user_id",typeof(int)),
            new DataColumn("user_name",typeof(string)),
            new DataColumn("login_name",typeof(string)),
            new DataColumn("user_pwd",typeof(string)),
            new DataColumn("user_sex",typeof(string)),
            new DataColumn("user_qq",typeof(string)),
            new DataColumn("user_email",typeof(string)),
            new DataColumn("user_phone", typeof(string)),
            new DataColumn("user_address",typeof(string)),
            new DataColumn("user_birth",typeof(DateTime)),
            new DataColumn("user_note",typeof(string)),
            new DataColumn("user_img",typeof(string)),
            new DataColumn("user_guid",typeof(string)),
            new DataColumn("reg_from",typeof(int)),
            new DataColumn("user_type",typeof(string)),
            new DataColumn("user_identify",typeof(string)),
            new DataColumn("status",typeof(int)),
            new DataColumn("max_machine",typeof(int)),
            new DataColumn("batch_user",typeof(bool)),
            new DataColumn("user_unit",typeof(string)),
            new DataColumn("buy_gold",typeof(decimal)),
            new DataColumn("give_gold",typeof(decimal)),
            new DataColumn("studentId",typeof(string))
            });


            foreach (UserModel userModel in userList) {
                DataRow row = dt.NewRow();
                row["user_name"] = userModel.user_name;
                row["login_name"] = userModel.login_name;
                row["user_pwd"] = EncryptHelper.Md5Encryption(userModel.user_pwd);
                row["user_sex"] = string.IsNullOrEmpty(userModel.user_sex) ? "男": userModel.user_sex;
                row["user_qq"] = userModel.user_qq;
                row["user_email"] = userModel.user_email;
                row["user_phone"] = userModel.user_phone;
                row["user_address"] = userModel.user_address;
                row["user_birth"] =DBNull.Value;
                row["user_note"] = importCode;
                row["user_img"] = DBNull.Value;
                row["user_guid"] = EncryptHelper.CreateGuidValue();
                row["reg_from"] = 0;
                row["user_type"] = userModel.user_type;
                row["user_identify"] = userModel.user_identify;
                row["status"] = userModel.status;
                row["max_machine"] = 0;
                row["batch_user"] = true;
                row["user_unit"] = userModel.user_unit;
                row["buy_gold"] = 0;
                row["give_gold"] = 0;
                row["studentId"] = userModel.login_name;

                dt.Rows.Add(row);
            }

            return dt;
        }

        /// <summary>
        /// 验证数据库是否已经存在学号
        /// </summary>
        /// <param name="studentId">学号</param>
        /// <returns>false不存在、true存在</returns>
        private bool checkStudentId(string studentId) {
            //T_Student student = DBSession.IT_StudentDAL.FindSingle(w=>w.studentId==studentId);

            StudentId s = studentIdList.FirstOrDefault(w=>w.studentId.Equals(studentId));
            if (s != null) {
                return true;
            }
            return false;
        }

        /// <summary>
        /// 生成账号列表
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        private void addStudentUserList(DataTable data,string schoolName,ref List<UserModel> UserModelList)
        {
            foreach (DataRow row in data.Rows) {
                UserModel userModel = new UserModel();
                userModel.login_name = Convert.ToString(row["F2"]);
                userModel.user_name = Convert.ToString(row["F1"]);
                userModel.user_pwd = Convert.ToString(row["F2"]);
                if (row["F5"] != null && !row["F5"].ToString().Equals("") && Convert.ToBoolean(row["F5"]))
                {
                    userModel.user_sex = "男";
                }
                if (row["F5"] != null && !row["F5"].ToString().Equals("") && Convert.ToBoolean(row["F5"])==false)
                {
                    userModel.user_sex = "女";
                }
                userModel.user_identify = Convert.ToString(row["F3"]);
                userModel.user_type = "学生";
                userModel.user_role = "9";
                userModel.status = 1;
                userModel.user_unit = schoolName;
                UserModelList.Add(userModel);
            }
        }
    }
}
